At 7:51 PM -0700 25/7/12, Adrian Klaver wrote:
>On 07/25/2012 07:47 PM, Chris Bartlett wrote:
>>At 7:37 PM -0700 25/7/12, Adrian Klaver wrote:
>>>I am guessing if you do show search_path; from psql you will see that
>>>the public schema is before the bob schema. The SELECT for the
>>>unqualified people table in CREATE VIEW bob.people_view will find
>>>public.people first in that case.
>>
>>I don't think that's it:
>>
>>show search_path
>>-> "$user",public
>>
>>select SESSION_USER
>>-> bob
>>
>> From the docs:
>>"The value for search_path must be a comma-separated list of schema
>>names. If one of the list items is the special value $user, then the
>>schema having the name returned by SESSION_USER is substituted, if there
>>is such a schema. (If not, $user is ignored.)"
>
>I see your point, but see below.
>
>http://www.postgresql.org/docs/9.2/static/runtime-config-client.html
>"
>If one of the list items is the special name $user, then the schema
>having the name returned by SESSION_USER is substituted, if there is
>such a schema and the user has USAGE permission for it. (If not,
>$user is ignored.)"
>
>In this version there is the qualifier that the user must have USAGE
>privileges on the schema. Is that the case?
>
>\dn+ should confirm.
Ah! The bob schema has no access privileges set. I had used pgAdmin3
- the schema definition pgAdmin3 reports is:
CREATE SCHEMA bob AUTHORIZATION bob;
(i.e., no grants)
So:
GRANT ALL ON SCHEMA bob TO bob;
DROP VIEW bob.people;
CREATE OR REPLACE VIEW bob.people_view AS SELECT people.name FROM people;
SELECT * FROM bob.people_view
-> returns records from bob.people as expected
I had created the bob schema using pgAdmin3 (connected as bob), but
when the schema owner is set to bob, there is no option in pgAdmin's
New Schema... setup to grant privileges on the schema to bob. I guess
I assumed that if bob owned the schema he would have all privileges
on that schema.
Thanks for pointing me in the right direction.